Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# $VARCACHE

The $VARCACHE property specifies whether Oracle OLAP stores or caches variable data that is the result of the execution of an AGGREGATE function or a $NATRIGGER expression.

See also: "How Oracle OLAP Determines Whether to Store or Cache Aggregated Data", "How Oracle OLAP Determines Whether to Store or Cache Results of $NATRIGGER", "What is an Oracle OLAP Session Cache?", and the description of the NA keyword of the CACHE statement for information on caching NA values calculated by the AGGREGATE function.

Syntax

You add or delete a $VARCACHE property to the most recently defined or considered object (see DEFINE and CONSIDER) using a PROPERTY statement:

- To add the property, issue the following statement.

  PROPERTY '$VARCACHE' value

- To delete the property, issue the following statement.

  PROPERTY DELETE '$VARCACHE'

Arguments

value

  One of the following TEXT expressions that indicate where Oracle OLAP should place variable data that is the result of calculations performed when the AGGREGATE function or $NATRIGGER value executes:

  - VARIABLE specifies that Oracle OLAP populates the variable with data that is the result of the execution of the AGGREGATE function or $NATRIGGER property. When you specify this option, the data that is the result of the aggregation is permanently stored in the variable when the analytic workspace is updated and committed.

  - SESSION specifies that Oracle OLAP caches data that is the result of the execution of the AGGREGATE function or $NATRIGGER property in the session cache (See "What is an Oracle OLAP Session Cache?"). When you specify this option, the data that is the result of the execution of the AGGREGATE function or $NATRIGGER property is ignored during updates and commits and is discarded at the end of the session.

    Important: When SESSCACHE is set to `NO`, Oracle OLAP does not cache the data even when you specify `SESSION`. In this case, specifying `SESSION` is the same as specifying `NONE`.

  - NONE specifies that Oracle OLAP calculates new variable data each time the AGGREGATE function or $NATRIGGER value executes; Oracle OLAP does not store or cache the data.

  - DEFAULT specifies that you do not want Oracle OLAP to use the $VARCACHE property when determining what to do with data that is calculated by the AGGREGATE function. (See "How Oracle OLAP Determines Whether to Store or Cache Aggregated Data".)

Notes

How Oracle OLAP Determines Whether to Store or Cache Results of $NATRIGGER

When a $NATRIGGER expression executes, what Oracle OLAP does with variable data that results from the execution of the expression is determined based on whether or not the variable that has the $NATRIGGER property also has a $STORETRIGGERVAL property and, if not, if the value of the $NATRIGGER property is an AGGREGATE function.

When a a $NATRIGGER expression executes, Oracle OLAP goes through the following process:

1. Does the variable with the $NATRIGGER property also have a $STORETRIGGERVAL property? If it does, then Oracle OLAP goes to step 1a. If it does not, then Oracle OLAP goes to step 2.

   1. Is the value of the TRIGGERSTOREOK option, `YES` or `NO`? If it is YES, then Oracle OLAP goes to step 1b. If it is `NO`, then Oracle OLAP goes to step 2.

   2. Is the value of the $STORETRIGGERVAL property, `YES` or `NO`? If it is YES, then Oracle OLAP stores the results of the $NATRIGGER expression and end decision-making process. If it is `NO`, then Oracle OLAP does not store the results of the $NATRIGGER expression and end decision-making process.

2. Is the $NATRIGGER expression an AGGREGATE function? If it is, then Oracle OLAP follows the steps described in "How Oracle OLAP Determines Whether to Store or Cache Aggregated Data" to determine what to do with the result of $NATRIGGER expression execution. If it is not, then Oracle OLAP goes to step 3.

3. Does the variable with the $NATRIGGER property also have a $VARCACHE property? If it does, then Oracle OLAP goes to step 4. If it does not, then Oracle OLAP goes to step 5.

4. Does the $VARCACHE property have a value of `DEFAULT`? If it does, then go to step 5. If it does not, then Oracle OLAP uses the value of the $VARCACHE property (that is, `STORE`, `CACHE`, or `NONE`) to determine what happens to the variable data values that are the result of $NATRIGGER expression execution and end decision-making process.

5. Use the current setting of the VARCACHE option to determine what happens to the variable data values that are the result of $NATRIGGER expression execution and end decision-making process.

How Oracle OLAP Determines Whether to Store or Cache Aggregated Data

When an AGGREGATE command executes, Oracle OLAP always stores the results of the calculation directly in the variable in the same way it stores the results of an assignment statement. However, when an AGGREGATE function executes, Oracle OLAP sometimes stores the results of the calculation directly in the variable and sometimes caches it in the session cache. (See "What is an Oracle OLAP Session Cache?" for more information about the session cache.)

To determine where to place the data that is the result of AGGREGATE function execution, Oracle OLAP goes through the following process to determine whether to store or cache aggregated variable data:

1. Is there a CACHE statement in the specification for the aggmap that is being used by the current AGGREGATE function? If there is, then Oracle OLAP goes to step 2. If there is not, then Oracle OLAP goes to step 3.

2. Is the CACHE statement a CACHE DEFAULT statement? If it is, then Oracle OLAP goes to step 3. If it is not, then Oracle OLAP uses the CACHE statement in the aggregation specification to determine what to do with variable data that is the result of the calculation and ends the decision-making process.

3. Does the variable being aggregated have a $VARCACHE property? If it does, then Oracle OLAP goes to Step 4. If it does not, then Oracle OLAP goes to step 5.

4. Does the $VARCACHE property have a value of DEFAULT? If it does, then Oracle OLAP goes to step 5. If it does not, then Oracle OLAP uses the value of the $VARCACHE property determines what happens to the variable data calculated using the AGGREGATE function, and ends the decision-making process.

5. Use the current setting of the VARCACHE option to determine what happens to the variable data calculated using the AGGREGATE function. End decision-making process.

Examples

Example 6-13 Setting the $VARCACHE Property

For a variable named `v1`, the following statements cause Oracle OLAP to cache the variable data that is the result of the execution of an AGGREGATE function or $NATRIGGER expression.

    CONSIDER v1
    PROPERTY '$SVARCACHE' 'v1'